package com.app.dao;

import com.app.base.BaseDao;
import com.app.entity.User;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.apache.ibatis.jdbc.SqlBuilder.*;

/**
 * Created by nanxin.cnx on 2015/7/15.
 */
@Repository("userDao")
public class UserDao extends BaseDao {
    private final Logger logger = LoggerFactory.getLogger(UserDao.class);

    public int batchInsert(final List<Map<String, Object>> paramList) {
        BEGIN();
        INSERT_INTO("user");
        VALUES("name", "?");
        VALUES("age", "?");
        VALUES("sex", "?");
        String sql = SQL();
        int result[] = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                int j=1;   //注意是从1开始的
                ps.setString(j++, String.valueOf(paramList.get(i).get("name")));
                ps.setInt(j++, Integer.valueOf(String.valueOf(paramList.get(i).get("age"))));
                ps.setString(j++, String.valueOf(paramList.get(i).get("sex")));
            }

            @Override
            public int getBatchSize() {
                return paramList.size();
            }
        });
        int k = 0;
        for (int i : result) {
            k += i;
        }
        return k;
    }

    public int batchUpdate(List<Map<String, Object>> paramList) {
        BEGIN();
        UPDATE("user");
        SET("age=:age");
        SET("name=:name");
        SET("sex=:sex");
        WHERE("id=:id");
        String sql = SQL();
        int result[] = namedParameterJdbcTemplate.batchUpdate(sql, paramList.toArray(new Map[paramList.size()]));
        int k=0;
        for (int i : result) {
            k += i;
        }
        return k;
    }

    public int insertUser(User user) {
        logger.info("before insert user");
        BEGIN();
        INSERT_INTO("user");
        VALUES("name", "?");
        VALUES("age", "?");
        VALUES("sex", "?");
        String sql = SQL();
        logger.info("user insert sql:"+sql);
        int result = this.jdbcTemplate.update(sql,user.getName(),user.getAge(),user.getSex());
        logger.info("after inser user");
        return result;
    }

    public int insertUserNamedParam(User user) {
        BEGIN();
        INSERT_INTO("user");
        if (StringUtils.isEmpty(user.getName()))
            VALUES("name", ":name");
        if (StringUtils.isEmpty(user.getAge()))
            VALUES("age", ":age");
        if (StringUtils.isEmpty(user.getSex()))
            VALUES("sex", ":sex");
        String sql = SQL();
        int result = this.namedParameterJdbcTemplate.update(sql, new BeanPropertySqlParameterSource(user));
        return result;
    }

    public List<Map<String, Object>> testOracleDual(List<String> list) {
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        String sql = "SELECT REGEXP_SUBSTR('%s','[^,]+',1,LEVEL) id" +
                "  FROM DUAL" +
                "  CONNECT BY LEVEL<=" +
                "  LENGTH('%s')-LENGTH(REPLACE('%s',','))+1";
        String sql2 = "select :list from dual";
        StringBuilder sb = new StringBuilder();
        for (String s : list) {
            sb.append(s).append(",");
        }
        sql = String.format(sql, sb.toString(), sb.toString(), sb.toString());
//        Map<String, Object> param = new HashMap<String, Object>();
//        param.put("list", sb.toString());
        result = this.jdbcTemplate.queryForList(sql2);
        return result;
    }


    public void testSimpleUpdate() {
        Map<String, Object> param = new HashMap<String, Object>();
//        param.put("id",17);
        param.put("name", "simpleInsert");
        param.put("age", 1);
//        param.put("sex", "男");
//        new SimpleJdbcInsert(jdbcTemplate).withTableName("user").execute(param);
        List<Map<String,Object>> list = jdbcTemplate.queryForList("select NAME from USER ", new Object[]{});
        for (Map<String, Object> m : list) {
            System.out.println(m.get("name"));
            System.out.println(m.get("NAME"));

        }
    }

    public void testJdbcTemplateInsertNull() {
        jdbcTemplate.update("insert into user(name,age) VALUES (?,?)", new Object[]{"testnull", null});
    }
}
